package com.oceansoft.core.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import com.oceansoft.core.common.util.PageUtil;
import com.oceansoft.core.common.util.Pagination;
import com.oceansoft.core.dao.IDictionaryDao;
import com.oceansoft.core.dao.mapper.DictMapper;
import com.oceansoft.core.domain.Dict;

@Repository
public class DictionaryDaoImpl implements IDictionaryDao {

	@Resource
	private JdbcTemplate jdbcTemplate;

	@Override
	public Pagination<Dict> findByPage(int pageIndex, Dict dict) {
		StringBuffer sql = new StringBuffer("SELECT * FROM T_SYS_DICT WHERE DEEP = 1");

		if (dict.getDbId()!=0) {
			sql.append(" AND parentId =").append(dict.getDbId());
		}
		if(StringUtils.hasLength(dict.getName())){
			sql.append(" AND NAME LIKE '%").append(dict.getName()).append("%'");
		}
		if (StringUtils.hasLength(dict.getParentName())) {
			sql.append(" AND PARENTNAME LIKE '%").append(dict.getParentName()).append("%'");
		}
		String count_sql = PageUtil.getCountSQL(sql.toString());
		int _total = jdbcTemplate.queryForInt(count_sql);
		sql.append(" ORDER BY PARENTID,ORDERNUM");
		String page_sql = PageUtil.getPageSQL(sql.toString(), pageIndex);
		List<Dict> list = jdbcTemplate.query(page_sql, new DictMapper());
		System.out.println(sql);
		return new Pagination<Dict>(list, _total, pageIndex);
	}

	@Override
	public List<Dict> findName() {
		String sql = "SELECT DBID, NAME FROM T_SYS_DICT WHERE DEEP = 0 ORDER BY ORDERNUM";
		return jdbcTemplate.query(sql, new RowMapper<Dict>() {
			@Override
			public Dict mapRow(ResultSet rs, int arg1) throws SQLException {
				Dict dict = new Dict();
				dict.setDbId(rs.getLong("DBID"));
				dict.setName(rs.getString("NAME"));
				return dict;
			}
		});
	}

	@Override
	public void addDict(Dict dict) {
		String sql = "INSERT INTO T_SYS_DICT (DBID,NAME,REMARK,READONLY,DEEP,ORDERNUM,PARENTID,PARENTNAME,CODE) VALUES(SEQ_SYS_DICT.NEXTVAL,?,?,?,?,?,?,?,?)";
		jdbcTemplate.update(sql, dict.getName(), dict.getRemark(), dict.getReadonly(), 1, dict.getOrderNum(),
				dict.getParentId(), dict.getParentName() ,dict.getCode());
	}

	@Override
	public void delete(long dbId) {
		String sql = "delete from t_sys_dict where dbId=?";
		jdbcTemplate.update(sql, dbId);

	}

	@Override
	public void edit(Dict dict) {
		String sql = "UPDATE T_SYS_DICT SET NAME=?,REMARK=?,ORDERNUM=?,PARENTID=?,PARENTNAME=? WHERE DBID=?";
		jdbcTemplate.update(sql, dict.getName(), dict.getRemark(), dict.getOrderNum(), dict.getParentId(),
				dict.getParentName(), dict.getDbId());
	}

	@Override
	public Dict find(long parentId, String name) {
		String sql = "SELECT * FROM T_SYS_DICT WHERE PARENTID=? AND NAME=?";
		List<Dict> list = jdbcTemplate.query(sql, new DictMapper(), parentId, name);
		return list.size() > 0 ? list.get(0) : null;
	}

	@Override
	public List<Dict> findByParentId(long parentId) {
		String sql = "SELECT * FROM T_SYS_DICT WHERE PARENTID=? ORDER BY ORDERNUM";
		return jdbcTemplate.query(sql, new DictMapper(), parentId);
	}

	@Override
	public Dict findByDbId(long dbId) {
		String sql = "SELECT * FROM T_SYS_DICT WHERE DBID=?";
		List<Dict> list = jdbcTemplate.query(sql, new DictMapper(), dbId);
		return list.size() > 0 ? list.get(0) : null;
	}

	@Override
	public List<Dict> findValues(long dbId) {
		String sql = "SELECT DBID, NAME FROM T_SYS_DICT WHERE PARENTID=? ORDER BY ORDERNUM";
		return jdbcTemplate.query(sql, new RowMapper<Dict>() {
			@Override
			public Dict mapRow(ResultSet rs, int arg1) throws SQLException {
				Dict dict = new Dict();
				dict.setDbId(rs.getLong("DBID"));
				dict.setName(rs.getString("NAME"));
				return dict;
			}
		}, dbId);
	}
	
	@Override
	public List<Dict> findSysDictValues(String lb,String type) {
		String sql = "SELECT CODE, NAME FROM T_SYS_DICT WHERE DICT_TYPE=? AND SUBSTR(CODE,1,1)=? ORDER BY ORDERNUM";
		return jdbcTemplate.query(sql, new RowMapper<Dict>() {
			@Override
			public Dict mapRow(ResultSet rs, int arg1) throws SQLException {
				Dict dict = new Dict();
				dict.setCode(rs.getString("CODE"));
				dict.setName(rs.getString("NAME"));
				return dict;
			}
		}, type,lb);
	}

	@Override
	public List<Dict> findSysDictValues(String type) {
		String sql = "SELECT CODE, NAME FROM T_SYS_DICT WHERE DICT_TYPE=? ORDER BY ORDERNUM";
		return jdbcTemplate.query(sql, new RowMapper<Dict>() {
			@Override
			public Dict mapRow(ResultSet rs, int arg1) throws SQLException {
				Dict dict = new Dict();
				dict.setCode(rs.getString("CODE"));
				dict.setName(rs.getString("NAME"));
				return dict;
			}
		}, type);
	}
	
	@Override
	public Map<String, String> findDictsByDictType(String dictType) {
		Map<String, String> rt = new LinkedHashMap<String, String>();
		String sql = "SELECT DBID, NAME FROM T_SYS_DICT WHERE DEEP = 1 AND DICT_TYPE = ? ORDER BY ORDERNUM";
		List<Dict> list = jdbcTemplate.query(sql, new RowMapper<Dict>() {
			@Override
			public Dict mapRow(ResultSet rs, int rowNum) throws SQLException {
				return new Dict(rs.getLong("DBID"), rs.getString("NAME"));
			}

		}, dictType);
		for (Dict dict : list) {
			rt.put(String.valueOf(dict.getDbId()), dict.getName());
		}
		return rt;
	}

	@Override
	public int findParentId(int dbid) {
		String sql = "SELECT PARENTID FROM T_SYS_DICT WHERE DBID=?";
		return jdbcTemplate.queryForInt(sql, dbid);
	}

	@Override
	public LinkedHashMap<String, String> findGCDictItem() {
		LinkedHashMap<String, String> rt = new LinkedHashMap<String, String>();
		String sql = "SELECT DBID,NAME FROM T_SYS_DICT T WHERE T.PARENTID = 1 OR T.PARENTID = 2 ORDER BY T.PARENTID,T.ORDERNUM";
		List<Dict> list = jdbcTemplate.query(sql, new RowMapper<Dict>() {
			@Override
			public Dict mapRow(ResultSet rs, int rowNum) throws SQLException {
				return new Dict(rs.getLong("DBID"), rs.getString("NAME"));
			}

		});
		for (Dict dict : list) {
			rt.put(String.valueOf(dict.getDbId()), dict.getName());
		}
		return rt;
	}

	@Override
	public LinkedHashMap<String, Dict> findGCDict() {
		LinkedHashMap<String, Dict> rt = new LinkedHashMap<String, Dict>();
		String sql = "SELECT * FROM T_SYS_DICT T WHERE T.PARENTID = 1 OR T.PARENTID = 2 ORDER BY T.PARENTID,T.ORDERNUM";
		List<Dict> list = jdbcTemplate.query(sql, new RowMapper<Dict>() {
			@Override
			public Dict mapRow(ResultSet rs, int rowNum) throws SQLException {
				return new Dict(rs.getLong("DBID"), rs.getString("NAME"), rs.getLong("PARENTID"), rs
						.getString("PARENTNAME"));
			}

		});
		for (Dict dict : list) {
			rt.put(String.valueOf(dict.getDbId()), dict);
		}
		return rt;
	}
	@Override
	public LinkedHashMap<String, Dict> findJZDict() {
		LinkedHashMap<String, Dict> rt = new LinkedHashMap<String, Dict>();
		String sql = "SELECT * FROM T_SYS_DICT T WHERE T.PARENTID = '4' ORDER BY T.PARENTID,T.ORDERNUM";
		List<Dict> list = jdbcTemplate.query(sql, new RowMapper<Dict>() {
			@Override
			public Dict mapRow(ResultSet rs, int rowNum) throws SQLException {
				return new Dict(rs.getLong("DBID"), rs.getString("NAME"), rs.getLong("PARENTID"), rs
						.getString("PARENTNAME"));
			}
			
		});
		for (Dict dict : list) {
			rt.put(String.valueOf(dict.getDbId()), dict);
		}
		return rt;
	}
	@Override
	public LinkedHashMap<String, Dict> _findGCDict() {
		LinkedHashMap<String, Dict> rt = new LinkedHashMap<String, Dict>();
		String sql = "SELECT * FROM T_SYS_DICT T WHERE T.PARENTID = 1 ORDER BY T.PARENTID,T.ORDERNUM";
		List<Dict> list = jdbcTemplate.query(sql, new RowMapper<Dict>() {
			@Override
			public Dict mapRow(ResultSet rs, int rowNum) throws SQLException {
				return new Dict(rs.getLong("DBID"), rs.getString("NAME"), rs.getLong("PARENTID"), rs
						.getString("PARENTNAME"));
			}

		});
		for (Dict dict : list) {
			rt.put(String.valueOf(dict.getDbId()), dict);
		}
		return rt;
	}

	@Override
	public List<Dict> findValues(long dbId, long jm) {
		String sql="";
		if(jm==-2){
		 sql = "SELECT SUBDBID, NAME FROM T_SYS_DICT WHERE PARENTID=? and dict_type !='BZLB_1' AND dict_type !='BZLB_4' ORDER BY ORDERNUM";	
		}
		else if(jm==5){
			sql = "SELECT SUBDBID, NAME FROM T_SYS_DICT WHERE PARENTID=? ORDER BY ORDERNUM";
		}else{
			sql = "SELECT SUBDBID, NAME FROM T_SYS_DICT WHERE PARENTID=? and dict_type !='BZLB_1' ORDER BY ORDERNUM";
		}
		 
		return jdbcTemplate.query(sql, new RowMapper<Dict>() {
			@Override
			public Dict mapRow(ResultSet rs, int arg1) throws SQLException {
				Dict dict = new Dict();
				dict.setSubDbid(rs.getLong("SUBDBID"));
				dict.setName(rs.getString("NAME"));
				return dict;
			}
		}, dbId);
	}

	@Override
	public List<Dict> findDictValues(long dbId, int type) {
		List<Dict> list= new ArrayList<Dict>();
		String tabDict="T_SYS_DICT";
					 String sql = "SELECT DBID, NAME,REMARK,DICT_TYPE FROM "+tabDict+" WHERE PARENTID=? ORDER BY ORDERNUM";
					  list= jdbcTemplate.query(sql, new RowMapper<Dict>() {
						@Override
						public Dict mapRow(ResultSet rs, int arg1) throws SQLException {
							Dict dict = new Dict();
							dict.setDbId(rs.getLong("DBID"));
							dict.setName(rs.getString("NAME"));
							dict.setRemark(rs.getString("REMARK"));
							dict.setDict_type(rs.getString("DICT_TYPE"));
							return dict;
						}
					  }, dbId);
				   
		return list;
	}

	@Override
	public int checkcode(long parentid, String code) {
		// TODO Auto-generated method stub
		String sql = "select count(1) from t_sys_dict where parentid='"+parentid+"' and code='"+code+"'";
		int num = jdbcTemplate.queryForInt(sql);
		return num;
	}

	@Override
	public int getMaxCode(long parentid) {
		String sql = "select max(code) from t_sys_dict where parentid='"+parentid+"'";
		int num = jdbcTemplate.queryForInt(sql);
		return num;
	}
}
